package org.cgs.web.server.registration;

import java.io.IOException;
import java.sql.*;

import javax.servlet.ServletException;
import javax.servlet.http.*;

import org.apache.log4j.Logger;
import org.cgs.web.database.DatabaseConnector;

public final class AddSchool extends HttpServlet
{
	private static final long serialVersionUID = 1L;
	private static final Logger LOGGER = Logger.getLogger(AddSchool.class);

	@Override
	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
	{
		// Get School info from the web page (set to default value if not returned)
		String courseCode = (String) request.getSession().getAttribute("courseCode");
		boolean isInstructor = request.getParameter("isInstructor") == null ? true : Boolean.parseBoolean(request.getParameter("isInstructor"));
		String name = request.getParameter("name") == null ? "" : request.getParameter("name");
		String address = request.getParameter("address") == null ? "" : request.getParameter("address");
		String contact = request.getParameter("contact") == null ? "" : request.getParameter("contact");
		String phone = request.getParameter("phone") == null ? "" : request.getParameter("phone");
		int numStudents = request.getParameter("numStudents") == null ? 0 : Integer.parseInt(request.getParameter("numStudents"));
		String details = request.getParameter("details") == null ? "" : request.getParameter("details");
		String[] times = request.getParameterValues("times") == null ? new String[]
		{} : request.getParameterValues("times");
		String isFar = request.getParameter("isFar") == null ? "" : request.getParameter("isFar");

		// Check name for invalid characters
		name = name.replace("&", "AND");
		name = name.replace("#", "NUM");
		name = name.replace("%", "");
		name = name.replace("$", "");
		name = name.replace("@", "AT");

		// See if the school name exists in the database
		boolean schoolExists = false;
		boolean errorOccurred = false;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			con = DatabaseConnector.getInstance().getConnection();
			pstmt = con.prepareStatement("SELECT * FROM school WHERE name=? AND courseCode=?");
			pstmt.setString(1, name);
			pstmt.setString(2, courseCode);
			rs = pstmt.executeQuery();

			// If there is an entry in first spot of result set, school exists
			if (rs.next())
			{
				schoolExists = true;
				LOGGER.info("School " + name + " already exists for course " + courseCode);
			}
			else
			{
				try
				{
					rs.close();
				}
				catch (Exception e)
				{
					errorOccurred = true;
					LOGGER.error("Exception cleaning up result set", e);
				}

				try
				{
					pstmt.close();
				}
				catch (Exception e)
				{
					errorOccurred = true;
					LOGGER.error("Exception cleaning up prepared statement", e);
				}

				pstmt = con.prepareStatement("INSERT INTO school VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
				pstmt.setString(1, name);
				pstmt.setString(2, courseCode);
				pstmt.setString(3, address);
				pstmt.setString(4, contact);
				pstmt.setString(5, phone);
				pstmt.setInt(6, numStudents);
				pstmt.setString(7, details);

				if (isFar.length() == 0)
				{
					pstmt.setNull(8, Types.VARCHAR);
				}
				else
				{
					pstmt.setString(8, isFar);
				}

				pstmt.executeUpdate();

				try
				{
					// Close the PreparedStatement
					pstmt.close();
				}
				catch (Exception e)
				{
					errorOccurred = true;
					LOGGER.error("Exception cleaning up prepared statement", e);
				}

				// Insert the school times
				pstmt = con.prepareStatement("INSERT INTO school_times VALUES (?, ?, ?)");
				for (String time : times)
				{
					pstmt.setString(1, name);
					pstmt.setString(2, time);
					pstmt.setString(3, courseCode);
					pstmt.executeUpdate();
				} // End for loop
			} // End else
		} // End try block
		catch (Exception e)
		{
			errorOccurred = true;
			LOGGER.error("Exception thrown attempting to add new school", e);
		} // End catch block
		finally
		{
			try
			{
				// Close the ResultSet
				if (rs != null)
				{
					rs.close();
				}
			}
			catch (Exception e)
			{
				errorOccurred = true;
				LOGGER.error("Exception cleaning up result set", e);
			}

			try
			{
				// Close the PreparedStatement
				if (pstmt != null)
				{
					pstmt.close();
				}
			}
			catch (Exception e)
			{
				errorOccurred = true;
				LOGGER.error("Exception cleaning up prepared statement", e);
			}

			try
			{
				// Close the connection
				if (con != null)
				{
					con.close();
				}
			}
			catch (Exception e)
			{
				errorOccurred = true;
				LOGGER.error("Exception cleaning up connection to the database", e);
			}
		}

		if (schoolExists)
		{
			request.getRequestDispatcher("").forward(request, response);
		}
		else
		{
			if (errorOccurred)
			{
				request.getRequestDispatcher("").forward(request, response);
			}
			else
			{
				if (isInstructor)
				{
					response.sendRedirect("instructor/instructorMain.jsp");
				}
				else
				{
					response.sendRedirect("school/schoolMain.jsp");
				}
			}
		}
	} // End method
} // End class
